﻿using System;
using System.Collections.Generic;
using System.Text;
using RockCompany.Common.Lib;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using RockCompany.Common.Constant;
using RockCompany.Master.DTO;

namespace RockCompany.Master.Model
{
    public class SC03FormModel
    {
        private DBConnector dbConenctor;

        public SC03FormModel()
        {
            dbConenctor = new DBConnector();
        }

        public DataTable GetUsers(UserDTO userDTO)
        {
            string Sql = "SELECT 0 AS chk, USERNAME, HO_TEN, MS_NHAN_VIEN, TEN_NHOM_QLY, MS_NHOM_QLY" +
                            " FROM USERS" +
                            " WHERE USERNAME LIKE @USERNAME" +
                            " AND TEN_NHOM_QLY LIKE @GROUP";

            DataTable res = new DataTable();
            if (dbConenctor.connect())
            {
                // Clear parameters
                dbConenctor.removeParameters();

                // Add parameters
                dbConenctor.addParameters(ParaConst.P_USERNAME, userDTO.username + "%");
                dbConenctor.addParameters(ParaConst.P_GROUPNAME, userDTO.groupName + "%");
                res = dbConenctor.selectData(Sql);
                dbConenctor.close();
            }

            return res;
        }

        public DataTable GetGroups()
        {
            string Sql = "SELECT DISTINCT TEN_NHOM_QLY, MS_NHOM_QLY" +
                        " FROM NHOM_QL";


            DataTable res = new DataTable();
            if (dbConenctor.connect())
            {

                res = dbConenctor.selectData(Sql);
                dbConenctor.close();
            }

            return res;
        }

        public bool InsertUser(UserDTO userDTO, string loginUserName)
        {
            string Sql = "INSERT INTO USERS (USERNAME, PASSWORD, HO_TEN, MS_NHAN_VIEN, TEN_NHOM_QLY, MS_NHOM_QLY" +
                         " , CREATION_USER, CREATION_TIME, CREATION_PROGRAM_ID)" +
                         " VALUES (@USERNAME,  @PASSWORD, @HO_TEN, @MS_NHAN_VIEN, @TEN_NHOM_QLY, @MS_NHOM_QLY" +
                         " , @CREATION_USER, @CREATION_TIME, @CREATION_PROGRAM_ID)";


            if (dbConenctor.connect())
            {
                dbConenctor.removeParameters();

                dbConenctor.addParameters(ParaConst.P_USERNAME, userDTO.username);
                dbConenctor.addParameters(ParaConst.P_PASSWORD, userDTO.password);
                dbConenctor.addParameters(ParaConst.P_HO_TEN, userDTO.fullname);
                dbConenctor.addParameters(ParaConst.P_MS_NHAN_VIEN, userDTO.employeeNo);
                dbConenctor.addParameters(ParaConst.P_TEN_NHOM_QLY, userDTO.groupName);
                dbConenctor.addParameters(ParaConst.P_MS_NHOM_QLY, userDTO.groupCode);

                dbConenctor.addParameters(ParaConst.P_CREATION_USER, loginUserName);
                dbConenctor.addParameters(ParaConst.P_CREATION_TIME, DateTime.Today);
                dbConenctor.addParameters(ParaConst.P_CREATION_PROGRAM_ID, "SC03");

                int effectedRow = dbConenctor.excuteNonQuery(Sql);
                dbConenctor.close();
                return effectedRow > 0; // True if 1 row is inserted
            }

            return false;
        }

        public bool ModifyUser(UserDTO userDTO, string loginUserName)
        {
            string Sql = "UPDATE USERS" +
                        " SET PASSWORD = @PASSWORD, HO_TEN =@HO_TEN, MS_NHAN_VIEN =@MS_NHAN_VIEN, TEN_NHOM_QLY = @TEN_NHOM_QLY" +
                            " , MS_NHOM_QLY = @MS_NHOM_QLY, LAST_UPDATE_PROGRAM_ID = @LAST_UPDATE_PROGRAM_ID, LAST_UPDATE_USER = @LAST_UPDATE_USER" +
                            " , LAST_UPDATE_TIME = @LAST_UPDATE_TIME" +
                        " WHERE USERNAME = @USERNAME";


            if (dbConenctor.connect())
            {
                dbConenctor.removeParameters();

                dbConenctor.addParameters(ParaConst.P_USERNAME, userDTO.username);
                dbConenctor.addParameters(ParaConst.P_PASSWORD, userDTO.password);
                dbConenctor.addParameters(ParaConst.P_HO_TEN, userDTO.fullname);
                dbConenctor.addParameters(ParaConst.P_MS_NHAN_VIEN, userDTO.employeeNo);
                dbConenctor.addParameters(ParaConst.P_TEN_NHOM_QLY, userDTO.groupName);
                dbConenctor.addParameters(ParaConst.P_MS_NHOM_QLY, userDTO.groupCode);

                dbConenctor.addParameters(ParaConst.P_LAST_UPDATE_USER, loginUserName);
                dbConenctor.addParameters(ParaConst.P_LAST_UPDATE_TIME, DateTime.Today);
                dbConenctor.addParameters(ParaConst.P_LAST_UPDATE_PROGRAM_ID, "SC03");

                int effectedRow = dbConenctor.excuteNonQuery(Sql);
                dbConenctor.close();
                return effectedRow != CommonConstantIF.NO_ROW_EFFECT; // True if 1 row is modified
            }

            return false;
        }

        public bool DeleteUsers(List<string> checkedList)
        {
            string Sql = "DELETE FROM USERS" +
                        " WHERE USERNAME = @USERNAME";


            if (dbConenctor.connect())
            {
                foreach (string val in checkedList)
                {
                    dbConenctor.removeParameters();

                    dbConenctor.addParameters(ParaConst.P_USERNAME, val);

                    dbConenctor.excuteNonQuery(Sql);
                    
                    //return effectedRow == CommonConstantIF.NO_ROW_EFFECT; // True if 1 row is modified
                }
                dbConenctor.close();
            }

            return true;
        }
    }
}
